{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "!pip install psycopg2 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%sql postgresql://postgres:saturnlab@localhost:5432/bdmi-test"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Movies Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS Movies;\n",
    "CREATE TABLE Movies(title VARCHAR(50), year INT, director VARCHAR(50), length INT);\n",
    "INSERT INTO Movies VALUES('Database Wars', 1967, 'John Joe', 123);\n",
    "INSERT INTO Movies VALUES('The Databaser', 1992, 'John Bob', 190);\n",
    "INSERT INTO Movies VALUES('Database Wars', 1998, 'John Jim', 176);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM movies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%sql DROP TABLE IF EXISTS A; DROP TABLE IF EXISTS B;\n",
    "%sql CREATE TABLE A (x int, y int); CREATE TABLE B (x int, y int);\n",
    "for i in range(1,6):\n",
    "    %sql INSERT INTO A VALUES (:i, :i+1)\n",
    "for i in range(1,11,3):\n",
    "    %sql INSERT INTO B VALUES (:i, :i+2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM movies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT m.title \n",
    "FROM Movies m\n",
    "WHERE m.year <> ANY(SELECT year FROM Movie WHERE title = m.title);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT m1.title\n",
    "FROM movies m1, movies m2\n",
    "WHERE m1.title = m2.title AND m1.year <> m2.year;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Product Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql drop table if exists product;\n",
    "create table product(\n",
    "       pname        varchar primary key, -- name of the product\n",
    "       price        money,               -- price of the product\n",
    "       category     varchar,             -- category\n",
    "       manufacturer varchar NOT NULL     -- manufacturer\n",
    ");\n",
    "insert into product values('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');\n",
    "insert into product values('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');\n",
    "insert into product values('SingleTouch', 149.99, 'Photography', 'Canon');\n",
    "insert into product values('MultiTouch', 203.99, 'Household', 'Hitachi');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%sql select * from product;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * from Product \n",
    "WHERE category='Gadgets' and price > 20.0;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "SELECT Pname, Price, Manufacturer\n",
    "FROM Product;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT Pname, Price, Manufacturer\n",
    "FROM Product\n",
    "WHERE category='Gadgets';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM Product;\n",
    "\n",
    "SELECT\n",
    "    p.manufacturer, p.pname, p.price\n",
    "FROM \n",
    "    (SELECT distinct p0.Manufacturer\n",
    "     FROM Product p0\n",
    "     WHERE p0.price < 20.00) cp, -- this is a nested query!\n",
    "    Product p\n",
    "WHERE \n",
    "    p.manufacturer = cp.manufacturer and p.price >= 20.00"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Company Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "drop table if exists product; -- This needs to be dropped if exists, see why further down!\n",
    "drop table if exists company;\n",
    "create table company (\n",
    "    cname varchar primary key, -- company name uniquely identifies the company.\n",
    "    stockprice money, -- stock price is in money \n",
    "    country varchar); -- country is just a string\n",
    "insert into company values ('GizmoWorks', 25.0, 'USA');\n",
    "insert into company values ('Canon', 65.0, 'Japan');\n",
    "insert into company values ('Hitachi', 15.0, 'Japan');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%sql select * from company;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql drop table if exists product;\n",
    "create table product(\n",
    "       pname varchar primary key, -- name of the product\n",
    "       price money, -- price of the product\n",
    "       category varchar, -- category\n",
    "       manufacturer varchar, -- manufacturer\n",
    "       foreign key (manufacturer) references company(cname));\n",
    "\n",
    "insert into product values('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');\n",
    "insert into product values('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');\n",
    "insert into product values('SingleTouch', 149.99, 'Photography', 'Canon');\n",
    "insert into product values('MultiTouch', 203.99, 'Household', 'Hitachi');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    %sql insert into product values('MultiTouch', 203.99, 'Household', 'Google');\n",
    "except Exception as e:\n",
    "    print(e) \n",
    "    print(\"Rejected!\") "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT pname, price\n",
    "FROM product, company\n",
    "where manufacturer=cname and country='Japan';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql -- duplicate answer\n",
    "SELECT Country\n",
    "FROM Product, Company\n",
    "WHERE  Manufacturer=CName AND Category='Gadgets';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
